package data.dao.db;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import data.dao.DBManager;
import data.dao.ITruckDAO;
import data.model.Truck;

public class DBTruckDAO implements ITruckDAO{
    private static final Logger LOGGER = Logger.getLogger(DBTruckDAO.class.getName());
    
    public static final String TABLE_NAME         = "TRUCK";
    
    private static final String ID_FIELD_NAME     = "ID";
    private static final String WEIGHT_FIELD_NAME = "WEIGHT";
    private static final String MP_FIELD_NAME     = "MP";
    private static final String LENGTH_FIELD_NAME = "LENGTH";
    private static final String HEIGHT_FIELD_NAME = "HEIGHT";   
    private static final String WIDTH_FIELD_NAME  = "WIDTH";

    
    private static final String ID_FIELD_TYPE     = "INTEGER PRIMARY KEY ";
    private static final String WEIGHT_FIELD_TYPE = "INT";
    private static final String MP_FIELD_TYPE     = "INT";
    private static final String LENGTH_FIELD_TYPE = "INT";
    private static final String HEIGHT_FIELD_TYPE = "INT";
    private static final String WIDTH_FIELD_TYPE  = "INT"; 
  
    
    public static final String CREATE_TABLE_STATEMENT = ID_FIELD_NAME + " " + ID_FIELD_TYPE 
	+ ", " + WEIGHT_FIELD_NAME        + " " + WEIGHT_FIELD_TYPE 
	+ ", " + MP_FIELD_NAME            + " " + MP_FIELD_TYPE 
	+ ", " + LENGTH_FIELD_NAME        + " " + LENGTH_FIELD_TYPE 
	+ ", " + HEIGHT_FIELD_NAME        + " " + HEIGHT_FIELD_TYPE 
	+ ", " + WIDTH_FIELD_NAME         + " " + WIDTH_FIELD_TYPE ;

    
    private PreparedStatement GET_ALL_TRUCKS_PSTATEMENT    = null;
    private PreparedStatement GET_USER_TRUCKS_PSTATEMENT   = null;
    private PreparedStatement UPDATE_TRUCK_PSTATEMENT      = null;
    private PreparedStatement INSERT_TRUCK_PSTATEMENT      = null;
    private PreparedStatement DELETE_ALL_TRUCKS_PSTATEMENT = null;
    
    private int lastId = Truck.NO_ID;
    
    public DBTruckDAO() {
    }
    
    public List<Truck> getTrucks() {
	return getTrucksWithIDFilter(null);
    }
    
    public Truck getTruck(int id) {
	Collection<Truck> trucks = getTrucksWithIDFilter(id);
	Truck truck;
	if (trucks.size() == 1) {
	    truck = trucks.iterator().next();
	} else {
	    truck = null;
	}
	return truck;
    }
    
    private List<Truck> getTrucksWithIDFilter(Integer id) {
	if (GET_ALL_TRUCKS_PSTATEMENT == null) {
	    GET_ALL_TRUCKS_PSTATEMENT = DBManager.INSTANCE.prepareStatement("SELECT * from " 
									    + TABLE_NAME 
									    + " order by "
									    + ID_FIELD_NAME);
	}
	if (GET_USER_TRUCKS_PSTATEMENT == null) {
	    GET_USER_TRUCKS_PSTATEMENT = DBManager.INSTANCE.prepareStatement("SELECT * from " 
									     + TABLE_NAME 
									     + " where "
									     + ID_FIELD_NAME 
									     + "=?"
									     + " order by " 
									     + ID_FIELD_NAME);
	}
	
	ResultSet resultSet;
	try {
	    PreparedStatement pStatement;
	    if (id != null) {
		pStatement = GET_USER_TRUCKS_PSTATEMENT;
		pStatement.setInt(1, id);
		
	    } else {
		pStatement = GET_ALL_TRUCKS_PSTATEMENT;
	    }
	    
	    resultSet = pStatement.executeQuery();
	} catch (SQLException e) {
	    LOGGER.log(Level.SEVERE, "Error while getting trucks", e);
	    resultSet = null;
	}
	
	List<Truck> result;
	if (resultSet == null) {
	    result = new ArrayList<Truck>();
	} else {
	    result = new ArrayList<Truck>();
	    try {
		while (resultSet.next()) {
		    Truck truck = new Truck();
		    truck.setId(resultSet.getInt(ID_FIELD_NAME));
		    truck.setWeight(resultSet.getInt(WEIGHT_FIELD_NAME));
		    truck.setMp(resultSet.getInt(MP_FIELD_NAME));
		    truck.setLength(resultSet.getInt(LENGTH_FIELD_NAME));
		    truck.setHeight(resultSet.getInt(HEIGHT_FIELD_NAME));
		    truck.setWidth(resultSet.getInt(WIDTH_FIELD_NAME));
		   
		    
		    result.add(truck);
		}
	    } catch (SQLException e) {
		e.printStackTrace();
		result = null;
	    }
	}
	
	return result;
    }
    
    private int getNextTruckId() {
	if (lastId == Truck.NO_ID) {
	    Collection<Truck> trucks = getTrucks();
	    for (Iterator<Truck> truckIte = trucks.iterator(); truckIte.hasNext();) {
		Truck truck = truckIte.next();
		if (truck.getId() > lastId) {
		    lastId = truck.getId();
		}
	    }
	}
	return ++lastId;
    }
    

    public boolean modify(Truck truck){
	boolean result = true;
	// modification
	try {
	    if (UPDATE_TRUCK_PSTATEMENT == null) {
		UPDATE_TRUCK_PSTATEMENT = DBManager.INSTANCE.prepareStatement("UPDATE " 
									      + TABLE_NAME 
									      + "SET "
									      + WEIGHT_FIELD_NAME 
									      + "=?, " 
									      + MP_FIELD_NAME 
									      + "=?, " 
									      + LENGTH_FIELD_NAME 
									      + "=?, "
									      + HEIGHT_FIELD_NAME 
									      + "=?, " 
									      + WIDTH_FIELD_NAME 
									      + "=?, " 	      
									      + " where " 
									      + ID_FIELD_NAME 
									      + "=?");
	    } else {
		UPDATE_TRUCK_PSTATEMENT.clearParameters();
	    }
	    
	    UPDATE_TRUCK_PSTATEMENT.setInt(1,  truck.getId());
	    UPDATE_TRUCK_PSTATEMENT.setInt(2, truck.getWeight());
	    UPDATE_TRUCK_PSTATEMENT.setInt(3, truck.getMp());
	    UPDATE_TRUCK_PSTATEMENT.setInt(4, truck.getHeight());
	    UPDATE_TRUCK_PSTATEMENT.setInt(5, truck.getWidth());	
	    
	    result = UPDATE_TRUCK_PSTATEMENT.executeUpdate() >= 1;
	    
	} catch (SQLException e) {
	    LOGGER.log(Level.SEVERE, "Error while updating truck", e);
	    result = false;
	}
	return true;
    }
    
    







    public boolean add(Truck truck) {
	boolean result;
	
	// ajout
	truck.setId(getNextTruckId());
	
	try {
	    if (INSERT_TRUCK_PSTATEMENT == null) {
		INSERT_TRUCK_PSTATEMENT = DBManager.INSTANCE.prepareStatement("INSERT INTO " 
									      + TABLE_NAME 
									      + " ("
									      + ID_FIELD_NAME 
									      + ", " 
									      + WEIGHT_FIELD_NAME 
									      + ", " 
									      + MP_FIELD_NAME 
									      + ", "
									      
									      + LENGTH_FIELD_NAME 
									      + ", " 
									      + HEIGHT_FIELD_NAME 
									      + ", " 
									      + WIDTH_FIELD_NAME 
									      + ") values (?, ?, ?, ?, ?, ?);");
	    } else {
		INSERT_TRUCK_PSTATEMENT.clearParameters();
		}
	    INSERT_TRUCK_PSTATEMENT.setInt(1, truck.getId());
	    INSERT_TRUCK_PSTATEMENT.setInt(2, truck.getWeight());
	    INSERT_TRUCK_PSTATEMENT.setInt(3, truck.getMp());
	    INSERT_TRUCK_PSTATEMENT.setInt(4, truck.getHeight());
	    INSERT_TRUCK_PSTATEMENT.setInt(5, truck.getWidth());	
	    
	    
	    result = INSERT_TRUCK_PSTATEMENT.executeUpdate() >= 1;
	} catch (SQLException e) {
	    LOGGER.log(Level.SEVERE, "Error while inserting truck", e);
	    result = false;
	}
    
	
	return result;
    }
    
    public boolean deleteAllTrucks() {
	if (DELETE_ALL_TRUCKS_PSTATEMENT == null) {
	    DELETE_ALL_TRUCKS_PSTATEMENT = DBManager.INSTANCE.prepareStatement("DELETE FROM " + TABLE_NAME);
	}
	boolean result;
	try {
	    result = DELETE_ALL_TRUCKS_PSTATEMENT.executeUpdate() >= 1;
	} catch (SQLException e) {
	    LOGGER.log(Level.SEVERE, "Error while deleting all trucks", e);
	    result = false;
	}
	if (result) {
	    lastId = Truck.NO_ID;
	}
	return result;
    }
}
